﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CubeDAL;
using SysInfo;

namespace DataProcess
{
    class DataProcess
    {
        /*static void Main(string[] args)
        {
            string dataInPath = "测试案例_p.txt";
            string dataOutPath = "data.out";
            string insertSQLPath = "insertSQL.in";
            string defaultReadingEncoding = "GB2312";
            DataProcess instance = new DataProcess();
            instance.getInsertSQL(dataInPath, dataOutPath, insertSQLPath, defaultReadingEncoding);
        }*/
        private StreamWriter swterr;
        private MySQLWrapper wrapper;
        private int failNumber;
        private int totalNumber;
        private int start_id;
        private importExcel.MainWindow window;
        public DataProcess(string dataInPath, importExcel.MainWindow w,string defaultReadingEncoding = "gb2312")
        {
            string sqlServer = Constant.DB_SERVER;
            string dataSqlDatabase = Constant.DB_DATABASE;
            string sqlUser = Constant.DB_USER;
            string sqlPasswd = Constant.DB_PASSWORD;
            window = w;
            wrapper = new MySQLWrapper(sqlServer, dataSqlDatabase, sqlUser, sqlPasswd);
            swterr = new StreamWriter("error.log",true, Encoding.GetEncoding("utf-8"));
            swterr.WriteLine("File:"+dataInPath+" time:"+System.DateTime.Now.ToString());
          //  start_id = id; need to detect the start_id
            getInsertSQL(dataInPath, defaultReadingEncoding);
            swterr.Close();
        }



        private void getInsertSQL(string dataInPath,string defaultReadingEncoding)
        {
            readFile(dataInPath, defaultReadingEncoding);
        }
        // 读取文件
        private void readFile(string dataInPath,string defaultReadingEncoding)
        {
            StreamReader srd;
            try
            {
                Encoding fileEncoding = GetEncoding(dataInPath, Encoding.GetEncoding(defaultReadingEncoding));
                srd = new StreamReader(dataInPath, fileEncoding);//用该编码创建StreamReader
               // swr = new StreamWriter(dataOutPath, false, fileEncoding);
            }
            catch
            {
                Console.WriteLine("{0} open faild\n", dataInPath);
                return;
            }

            processData(wrapper, srd);
            srd.Close();
        }
        /**格式化数据*/
        private void processData(MySQLWrapper insertSQLPath, StreamReader srd)
        {
            int id = 1;
            while (srd.Peek() != -1)
            {
                String str = srd.ReadLine();
                char[] septor = { '\t' };
                string[] line = str.Split(septor);
                string[] sqlline = new string[400];
                int[] timecolum = { 5, 71, 72 };//需要转化时间的列
                int[] isInt = { 2, 3, 4, 7, 8, 10, 11, 12, 14, 16, 18 };
                int errDateTime = 0;
                if (line != null && line.Length == 0) continue;
                for (int i = 0; i < timecolum.Length; i++)
                {
                    string date = str2DateTime(line[timecolum[i]]);
                    if (date.Equals("-1")) errDateTime = 1;
                    else line[timecolum[i]] = date;
                }
                if (errDateTime == 1)
                {
                    
                    try
                    {
                        swterr.Write("Error happened in line " + id.ToString() + ": ");
                        for (int i = 0; i < line.Length; i++)
                            if (i == 0)
                                swterr.Write("{0}", line[i]);
                            else
                                swterr.Write("\t{0}", line[i]);
                        swterr.WriteLine();
                        failNumber++;
                        totalNumber++;
                        window.updateProgress(totalNumber);
                    }
                    catch
                    {
                        Console.WriteLine("insertSQL.in open faild\n");
                        continue;
                    }
                    id++;
                    continue;
                }
                for (int i = 0; i < isInt.Length; i++)
                {
                }
                for (int i = 0; i < line.Length; i++)
                {
                    sqlline[i] = line[i];
                    if (line[i].Equals("是"))
                    {
                    //    swr.Write("1\t");
                        sqlline[i] = "1";
                    }
                    else if (line[i].Equals("否"))
                    {
                    //    swr.Write("0\t");
                        sqlline[i] = "0";
                    }
                    //else
                    //    swr.Write("{0}\t", line[i]);

                }
                //swr.WriteLine();
                insertSQL(insertSQLPath, sqlline, line.Length - 1, id);
                id++;
            }
            window.alertEnd(failNumber);
        }

        /**生成insert */
        private void insertSQL(MySQLWrapper insertSQLPath, string[] sqlline, int len, int id)
        {
            string sql = "insert into `yuqing` values( null"; 
            int i, j;
            int[] columName = { 6, 15, 17 };//需要跳过的列
            int[] yesOrNo = { 1, 41, 42, 43, 44, 45, 46, 56, 58, 59, 60, 61, 62, 63, 116, 119, 120, 121, 122, 123 };
            
            //int cnt = 1;
            for (i = 0; i < len; i++)
            {
                for (j = 0; j < columName.Length; j++)
                    if (columName[j] == i)
                        break;
                if (j < columName.Length) continue;

                string str = sqlline[i];
                if (str == null || str == "")
                    sql += ", null";
                else
                {
                    sql += " ,'";
                    if (str == null || str == "")
                    {
                        str = "";
                        for (j = 0; j < yesOrNo.Length; j++)
                            if (yesOrNo[j] == i)
                            {
                                str = "0";
                                break;
                            }
                    }
                    //  Console.WriteLine(str);
                    str = str.Replace("'", "''");
                    //cnt++;
                    sql += str + "'";
                }
            }
            //Console.WriteLine("cnt=:{0}", cnt);
            sql += ");";
            StreamWriter swr;
            try
            {
               // System.Text
                int statuscode = wrapper.Insert(sql);
                if (statuscode == -1)
                {
                    failNumber++;
                    swterr.Write("Error happened in line " + id.ToString() + ": ");
                    for (int ii = 0; ii < sqlline.Length; ii++)
                        if (ii == 0)
                            swterr.Write("{0}", sqlline[ii]);
                        else
                            swterr.Write("\t{0}", sqlline[ii]);
                    swterr.WriteLine();
                }
                totalNumber++;
                window.updateProgress(totalNumber);
                //swr = new StreamWriter(insertSQLPath, true, Encoding.GetEncoding("utf-8"));
                //swr.Write("{0}", sql);
                //swr.WriteLine();
                //swr.Close();

            }
            catch
            {
                Console.WriteLine("insertSQL.in open faild\n");
                return;
            }

        }
        /**格式化日期*/
        private string str2DateTime(String dateStr)
        {
            string year = "", month = "", day = "";
            int dateFormat = 1;
            dateStr.Replace("时", ":");
            dateStr.Replace("分", ":");
            dateStr.Replace("秒", "");
            //处理 YYYY年MM月DD日
            if (dateStr.Contains("年") && dateStr.Contains("月") && dateStr.Contains("日"))
            {
                year = dateStr.Substring(0, dateStr.IndexOf("年"));
                month = dateStr.Substring(dateStr.IndexOf("年") + 1, dateStr.IndexOf("月") - dateStr.IndexOf("年") - 1);
                day = dateStr.Substring(dateStr.IndexOf("月") + 1, dateStr.IndexOf("日") - dateStr.IndexOf("月") - 1);
                int i = dateStr.IndexOf("日") + 1;
                day += " ";
                bool flag = true;
                while (i < dateStr.Length && flag)
                    if ((dateStr[i] >= '0' && dateStr[i] <= '9') || (dateStr[i].Equals(':') || dateStr[i].Equals('：')))
                        i++;
                    else
                        flag = false;
                i = dateStr.IndexOf("日");
                while (flag && ++i < dateStr.Length)
                    if (dateStr[i].Equals('：'))
                        day += ':';
                    else
                        day += dateStr[i];
            }
            else if (dateStr.Contains("/") || dateStr.Contains("-"))
            {
                //处理 YYYY/MM/DD/ time
                char[] septor = { '/' };
                if (dateStr.Contains("-"))
                    septor[0] = '-';
                string[] date = dateStr.Split(septor);
                if (date.Length == 3)
                {
                    year = date[0];
                    month = date[1];
                    day = date[2];
                }
                else dateFormat = 0;
            }
            else
                dateFormat = 0;//valid date format

            //return value
            if (dateFormat == 1)
            {
                return year + "-" + month + "-" + day;
            }
            else
                return "-1";

        }

        /**取得一个文本文件的编码方式。*/
        private Encoding GetEncoding(string fileName)
        {
            return GetEncoding(fileName, Encoding.Default);
        }

        /// 取得一个文本文件流的编码方式。
        private Encoding GetEncoding(FileStream stream)
        {
            return GetEncoding(stream, Encoding.Default);
        }

        /** 取得一个文本文件的编码方式。
        <param name="defaultEncoding">默认编码方式。
         当该方法无法从文件的头部取得有效的前导符时，将返回该编码方式。</param>
         * */
        private Encoding GetEncoding(string fileName, Encoding defaultEncoding)
        {
            FileStream fs = new FileStream(fileName, FileMode.Open);
            Encoding targetEncoding = GetEncoding(fs, defaultEncoding);
            fs.Close();
            return targetEncoding;
        }


        private Encoding GetEncoding(FileStream stream, Encoding defaultEncoding)
        {
            Encoding targetEncoding = defaultEncoding;
            if (stream != null && stream.Length >= 2)
            {
                //保存文件流的前4个字节
                byte byte1 = 0;
                byte byte2 = 0;
                byte byte3 = 0;
                byte byte4 = 0;
                //保存当前Seek位置
                long origPos = stream.Seek(0, SeekOrigin.Begin);
                stream.Seek(0, SeekOrigin.Begin);
                int nByte = stream.ReadByte();
                byte1 = Convert.ToByte(nByte);
                byte2 = Convert.ToByte(stream.ReadByte());
                if (stream.Length >= 3)
                {
                    byte3 = Convert.ToByte(stream.ReadByte());
                }

                if (stream.Length >= 4)
                {
                    byte4 = Convert.ToByte(stream.ReadByte());
                }

                //根据文件流的前4个字节判断Encoding
                //Unicode {0xFF, 0xFE};
                //BE-Unicode {0xFE, 0xFF};
                //UTF8 = {0xEF, 0xBB, 0xBF};
                if (byte1 == 0xFE && byte2 == 0xFF)//UnicodeBe
                {
                    targetEncoding = Encoding.BigEndianUnicode;
                }
                if (byte1 == 0xFF && byte2 == 0xFE && byte3 != 0xFF)//Unicode
                {
                    targetEncoding = Encoding.Unicode;
                }
                if (byte1 == 0xEF && byte2 == 0xBB && byte3 == 0xBF)//UTF8
                {
                    targetEncoding = Encoding.UTF8;
                }
                //恢复Seek位置　　　 
                stream.Seek(origPos, SeekOrigin.Begin);
            }
            return targetEncoding;
        }
    }
}
